﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace API.Models.Repository
{
    public class COMPANIES_MAPPING_Repository : GenericRepository<PartnerDbContext, COMPANIES_MAPPING>
    {
        public COMPANY_MAP GetMapping(Guid SUPPLIER_GUID)
        {
            var supplier_repository = new Suppliers_Repository();
            var company_repository = new Companies_Repository();

            supplier_repository.SetSession(this.session);
            company_repository.SetSession(this.session);

            var LEFT_COMPANY = supplier_repository
                .Find(r => r.GUID_RECORD == SUPPLIER_GUID)
                .SingleOrDefault();

            var COMPANIES = company_repository.Find().ToList();

            if (!COMPANIES.Any())
                return new COMPANY_MAP
                {
                    LEFT_COMPANY_MAP = LEFT_COMPANY
                };

            var sql = @"
            SELECT columnName, GUID_RECORD
            FROM 
               (SELECT 
	            {2}
               FROM WH_COMPANIES_MAPPING 
               WHERE [{0}] = '{1}') p
            UNPIVOT
               (GUID_RECORD FOR columnName IN 
                  ({2})
            ) AS unpvt
            WHERE columnName = GUID_RECORD;
            ";

            var sql_columns = String.Join(", ", 
                COMPANIES.Select(r => String.Format("[{0}]", r.GUID_RECORD)));

            sql = String.Format(sql, LEFT_COMPANY.OWNER_GUID, LEFT_COMPANY.GUID_RECORD, sql_columns);

            var data = this.Context.Database.SqlQuery<CommonDbClass>(sql).SingleOrDefault();
            
            if (data == null)
                return new COMPANY_MAP
                {
                    LEFT_COMPANY_MAP = LEFT_COMPANY
                };

            var RIGHT_COMPANY = COMPANIES
                .Where(r => r.GUID_RECORD == data.GUID_RECORD)
                .SingleOrDefault();

            return new COMPANY_MAP
            {
                LEFT_COMPANY_MAP = LEFT_COMPANY,
                RIGHT_COMPANY_MAP = RIGHT_COMPANY
            };
        }

        public void SetMapping(Guid COMPANY_GUID, Guid SUPPLIER_GUID)
        {
            var LEFT_COMPANY = _entities.WH_COMPANIES
                .SingleOrDefault(r => r.GUID_RECORD == COMPANY_GUID);

            var RIGHT_COMPANY = _entities.WH_COMPANIES
                .SingleOrDefault(r => r.GUID_RECORD == SUPPLIER_GUID);

            var COMPANIES = _entities.WH_COMPANIES
                .Where(r => !r.OWNER_GUID.HasValue)
                .ToList();

            var sql = @"
            DECLARE
	            @LEFT_LOCATION_GUID		uniqueidentifier,
	            @RIGHT_LOCATION_GUID	uniqueidentifier,
	            @LEFT_GUID_RECORD	uniqueidentifier,
	            @RIGHT_GUID_RECORD	uniqueidentifier;

            SET @LEFT_LOCATION_GUID = '{0}';
            SET @RIGHT_LOCATION_GUID = '{1}';

            SELECT
	            @LEFT_GUID_RECORD = GUID_RECORD
            FROM 
	            WH_COMPANIES_MAPPING
            WHERE 
	            [{2}] = @LEFT_LOCATION_GUID;

            SELECT
	            @RIGHT_GUID_RECORD = GUID_RECORD
            FROM 
	            WH_COMPANIES_MAPPING
            WHERE 
	            [{3}] = @RIGHT_LOCATION_GUID;

            IF @LEFT_GUID_RECORD = @RIGHT_GUID_RECORD BEGIN
	            -- Ничего не делаем
	            RETURN;
            END;

            IF @LEFT_GUID_RECORD IS NULL AND @RIGHT_GUID_RECORD IS NULL BEGIN

	            INSERT INTO WH_COMPANIES_MAPPING ([{2}], [{3}])
	            VALUES (@LEFT_LOCATION_GUID, @RIGHT_LOCATION_GUID);

	            SELECT 'INSERT NEW RECORD';
	            RETURN;
            END;

            IF @LEFT_GUID_RECORD IS NOT NULL AND @RIGHT_GUID_RECORD IS NOT NULL BEGIN

	            UPDATE WH_COMPANIES_MAPPING SET
		            {4}
	            WHERE
		            GUID_RECORD = @LEFT_GUID_RECORD;
	
	            DELETE WH_COMPANIES_MAPPING	WHERE
		            GUID_RECORD = @RIGHT_GUID_RECORD;

	            SELECT 'UPDATE RECORD AND DELETE';
	            RETURN;
            END;

            IF @LEFT_GUID_RECORD IS NOT NULL AND @RIGHT_GUID_RECORD IS NULL BEGIN
	
	            UPDATE WH_COMPANIES_MAPPING SET
		            [{3}] = @RIGHT_LOCATION_GUID
	            WHERE
		            GUID_RECORD = @LEFT_GUID_RECORD;

	            RETURN;
            END;

            IF @LEFT_GUID_RECORD IS NULL AND @RIGHT_GUID_RECORD IS NOT NULL BEGIN

	            UPDATE WH_COMPANIES_MAPPING SET
		            [{2}] = @LEFT_LOCATION_GUID
	            WHERE
		            GUID_RECORD = @RIGHT_GUID_RECORD;

	            RETURN;
            END;";

            sql = String.Format(sql,
                LEFT_COMPANY.GUID_RECORD, RIGHT_COMPANY.GUID_RECORD,
                LEFT_COMPANY.GUID_RECORD, RIGHT_COMPANY.OWNER_GUID,
                String.Join(", ", COMPANIES.Select(r => String.Format(
                    "[{0}] = ISNULL([{0}], (SELECT [{0}] FROM WH_COMPANIES_MAPPING WHERE GUID_RECORD = @RIGHT_GUID_RECORD))",
                    r.GUID_RECORD.ToString()))));

            this.Context.Database.ExecuteSqlCommand(sql);

            /// Перекодировка связанных с товаром документов
            var outgoing_invoices_repository = new OutgoingInvoices_Repository();
            var incoming_invoices_repository = new IncomingInvoices_Repository();

            var outgoing_invoices = outgoing_invoices_repository
                .Find(r => r.COMPANY_GUID == LEFT_COMPANY.GUID_RECORD || r.COMPANY_GUID == RIGHT_COMPANY.GUID_RECORD)
                .ToList();

            var incoming_invoices = incoming_invoices_repository
                .Find(r => r.COMPANY_GUID == LEFT_COMPANY.GUID_RECORD || r.COMPANY_GUID == RIGHT_COMPANY.GUID_RECORD)
                .ToList();

            foreach (var r in outgoing_invoices)
                outgoing_invoices_repository.Recode(r.GUID_RECORD);

            foreach (var r in incoming_invoices)
                outgoing_invoices_repository.Recode(r.GUID_RECORD);
        }
    }
}